package com.cloudcc.boot.service;

import com.cloudcc.boot.core.CCObject;
import com.cloudcc.boot.core.CCService;
import com.cloudcc.boot.core.ServiceResult;
import com.cloudcc.boot.core.UserInfo;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.Date;
import java.util.List;

/**
 * 用户导入工具类
 */
@CrossOrigin
@Controller
@RequestMapping(value = "/api")
@Slf4j
@Api(value = "", tags = {"Rustful"}, description = "")
public class userClass {
    public static UserInfo userInfo;
    @ResponseBody
    @RequestMapping(value = "/poi_execl/importuser", produces = "application/json;charset=utf-8", headers = "accept=text/*")
    @ApiOperation(value = "上传")
    public JSONObject parseExcel(@RequestParam("file") MultipartFile MPfile) {//@RequestParam("file") MultipartFile MPfile
        System.out.println("开始导入··················································");
        CCService cs = new CCService(userInfo);
        JSONArray dataArray = new JSONArray();

        HSSFWorkbook wb = null;
        try {
            wb = new HSSFWorkbook(MPfile.getInputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
        org.apache.poi.ss.usermodel.Sheet sheet1 = wb.getSheetAt(0);
        String returncontent = "";
        int count = 0;
        String retstatus = "";
        for (org.apache.poi.ss.usermodel.Row row : sheet1) {
            count++;
            if (count == 1) {
                continue;
            } else {
                //获取列数
                int lastColumn = row.getLastCellNum();
                int num = 0;
                org.apache.poi.ss.usermodel.Cell cell1 = row.getCell(0);
                String loginName = cell1 == null ? "" : getCellValue(cell1).trim();//登录名称(loginName)
//                System.out.println("loginName:"+loginName);
                org.apache.poi.ss.usermodel.Cell cell2 = row.getCell(1);
                String name = cell2 == null ? "" : getCellValue(cell2).trim();//用户姓名(name)
//                System.out.println("name:"+name);
                org.apache.poi.ss.usermodel.Cell cell3 = row.getCell(2);
                String email = cell3 == null ? "" : getCellValue(cell3).trim();//邮箱(email)
//                System.out.println("email:"+email);
                org.apache.poi.ss.usermodel.Cell cell4 = row.getCell(3);
                String role = cell4 == null ? "" : getCellValue(cell4).trim();//角色(role)
                //角色查询（根据名称）
                List<CCObject> roleList = cs.cqlQuery("role", "select * from role where rolename='" + role + "'");
                String roleID = "";
                if (roleList.size() > 0) {
                    roleID = roleList.get(0).get("roleid").toString();//角色ID
                }
//                System.out.println("role:"+role);
//                System.out.println("roleID:"+roleID);
                org.apache.poi.ss.usermodel.Cell cell5 = row.getCell(4);
                String profile = cell5 == null ? "" : getCellValue(cell5).trim();//简档(profile)
                //简档查询（根据名称）
                List<CCObject> profileList = cs.cqlQuery("tp_sys_profile", "select id from tp_sys_profile where profilename='" + profile + "'");
                String profileID = "";
                if (profileList.size() > 0) {
                    profileID = profileList.get(0).get("id").toString();//简档ID
                }
//                System.out.println("profile:"+profile);
//                System.out.println("profileID:"+profileID);
                org.apache.poi.ss.usermodel.Cell cell6 = row.getCell(5);
                String department = cell6 == null ? "" : getCellValue(cell6).trim();//部门(department)
//                System.out.println("department:"+department);
                org.apache.poi.ss.usermodel.Cell cell7 = row.getCell(6);
                String title = cell7 == null ? "" : getCellValue(cell7).trim();//岗位(title)
//                System.out.println("title:"+title);
                org.apache.poi.ss.usermodel.Cell cell8 = row.getCell(7);
                String manager = cell8 == null ? "" : getCellValue(cell8).trim();//上级经理(manager)
                List<CCObject> managerList = cs.cqlQuery("ccuser", "select id from ccuser where loginname='" + manager + "'");//用户查询，根据登陆名
                String managerId = "";
                if (managerList.size() > 0) {
                    managerId = managerList.get(0).get("id").toString();
                }
//                System.out.println("manager:"+manager);
//                System.out.println("managerId:"+managerId);
                org.apache.poi.ss.usermodel.Cell cell9 = row.getCell(8);
                String ssqy = cell9 == null ? "" : getCellValue(cell9).trim();//所属区域(ssqy)

                List<CCObject> userList = cs.cquery("ccuser", "loginname='" + loginName + "'"); //用户查询，根据登录名
                if (userList.size() == 0) {//根据员工编号判断该用户是否存在系统,不存在：调接口插入用户
                    ServiceResult return_value = cs.createUser(loginName,name,email,profileID,roleID,"CRRC@crm2025");
                    System.out.println("序号：" + count + "、姓名：" + name + "、登陆名：" + loginName + "不存在!插入数据：" + return_value + "</br>");
                }else {//根据员工编号判断该用户是否存在系统,存在：更新该用户数据
                    String userSql = " update ccuser set department='" + department + "',employeenum='" + loginName + "',title='" + title + "',manager='" + managerId
                            + "',ssqy='" + ssqy + "' where loginname='" + loginName + "'";
                    cs.cqlQuery("ccuser", userSql);
                    System.out.println("序号：" + count + "、姓名：" + name + "、登陆名：" + loginName + "已存在!更新数据</br>");
                }
            }
        }


        System.out.println("导入结束··················································");

        JSONObject returnjson = new JSONObject();
        returnjson.put("data", dataArray.toString());
        if (dataArray.size() > 0) {
            returnjson.put("msg", "部分订单导入失败，请检查并重新导入");
        } else {
            returnjson.put("msg", "导入成功");
        }
        return returnjson;
    }
    public String getCellValue(org.apache.poi.ss.usermodel.Cell cell) {
        String s = "";
        String yh ="";//服务人员用户的id
        String fw_id ="";//服务人员id
        String DATE_OUTPUT_PATTERNS = "yyyy-MM-dd";
        java.text.SimpleDateFormat simpleDateFormat = new java.text.SimpleDateFormat(DATE_OUTPUT_PATTERNS);
        String ret;
        if(cell == null) return "";
        switch (cell.getCellType()) {
            case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
                ret = "";
                break;
            case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
                ret = String.valueOf(cell.getBooleanCellValue());
                break;
            case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
                ret = null;
                break;
            case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
                org.apache.poi.ss.usermodel.Workbook wb = cell.getSheet().getWorkbook();
                org.apache.poi.ss.usermodel.CreationHelper crateHelper = wb.getCreationHelper();
                org.apache.poi.ss.usermodel.FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
                ret = getCellValue(evaluator.evaluateInCell(cell));
                break;
            case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    Date theDate = cell.getDateCellValue();
                    ret = simpleDateFormat.format(theDate);
                } else {
                    ret = org.apache.poi.ss.util.NumberToTextConverter.toText(cell.getNumericCellValue());
                }
                break;
            case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                ret = cell.getRichStringCellValue().getString();
                break;
            default:
                ret = null;
        }
        return ret; //有必要自行trim
    }
}